package com.xiia.dao;

import java.sql.SQLException;
import java.util.Collections;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import com.xiia.util.DBConnect;
import com.xiia.util.Pager;
import com.xiia.vo.Check;
import com.xiia.vo.Notice;
import com.xiia.vo.NoticeSort;

/**
 * 
 * @author zhouhao 2014-11-4 10:36
 * Notice的dao
 */
public class NoticeDao extends BaseDao {

	public List<Notice> listNoticeBySortId(NoticeSort noticeSort) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where sortId = ? "
				+ "order by lastUpdateTime desc";
		Object[] params = {noticeSort.getSortId()};
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			return notices;
		}
		System.out.println("查询公告出错");
		return null;
	}

	public Pager listPublishedNotice(NoticeSort noticeSort, Pager page) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where sortId = ? and status = ? "
				+ "order by lastUpdateTime desc limit ?, ?";
		Object[] params = {noticeSort.getSortId(), 
							5,                         //5表示已发布状态
							(page.getCurrentPage() - 1) * page.getPageSize(),
							page.getPageSize()}; 
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			page.setList(notices);
			return page;
		}
		System.out.println("查询公告出错");
		return null;
	}
	
	public List<Notice> listPublishedNotice(NoticeSort noticeSort) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where sortId = ? and status = ? "
				+ "order by lastUpdateTime desc";
		Object[] params = {noticeSort.getSortId(), 
							5};                         //5表示已发布状态
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			return notices;
		}
		System.out.println("查询公告出错");
		return null;
	}
	
	public Notice showNoticeById(int noticeId) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where noticeId = ?";
		Object[] params = {noticeId};
		Notice notice2 = (Notice) findObject(sql, params, Notice.class);
		if ( notice2 != null){
			return notice2;
		}
		System.out.println("根据id查询公告内容出错");
		return null;
	}
	/**
	 * 列出数据库中所有的公告
	 * @return
	 */
	public List<Notice> listAllNotice() {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice order by lastUpdateTime desc";
		List<Notice> notices = find(sql, null, Notice.class);
		if (notices != null){
			return notices;
		} 
		System.out.println("查询所有公告列表出错");
		return null;
	}
	
	public List<Notice> listAllPublishedNotice(){
		String sql = "select * from t_notice where status = ? "
				+ "order by lastUpdateTime desc";
		Object[] params = {5};                         //5表示已发布状态
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			return notices;
		}
		System.out.println("查询所有已发布公告出错");
		return null;
	}
	public boolean addNotice(Notice notice) {
		// TODO Auto-generated method stub
		String sql = "insert into t_notice(title, content, addTime, "
				+ "lastUpdateTime, status, sortId, userId) "
				+ "values(?, ?, ?, ?, ?, ?, ?)";
		Object[] params = {notice.getTitle(),
							notice.getContent(),
							notice.getAddTime(),
							notice.getLastUpdateTime(),
							notice.getStatus(),
							notice.getSortId(),
							notice.getUserId()};
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			query.update(conn, sql, params);
			sql = "select * from t_notice_sort where sortId = ?";
			Object[] params2 = {notice.getSortId()};
			//取出所属的公告分类的noticeNum字段
			NoticeSort noticeSort = (NoticeSort) 
					query.query(conn, sql, params2, new BeanHandler(NoticeSort.class));
			sql = "update t_notice_sort set noticeNum = ? where sortId = ?";
			Object[] params3 = {noticeSort.getNoticeNum() + 1, notice.getSortId()};
			//公告数量加1
			query.update(conn, sql, params3);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.out.println("插入公告出错");
			return false;
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}

	public boolean deleteNotice(Notice notice) {
		// TODO Auto-generated method stub
		String sql = "delete from t_notice where noticeId = ?";
		Object[] params = {notice.getNoticeId()};
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			query.update(conn, sql, params);
			sql = "select * from t_notice_sort where sortId = ?";
			Object[] params2 = {notice.getSortId()};
			//取出所属的公告分类的noticeNum字段
			NoticeSort noticeSort = (NoticeSort) 
					query.query(conn, sql, params2, new BeanHandler(NoticeSort.class));
			sql = "update t_notice_sort set noticeNum = ? where sortId = ?";
			Object[] params3 = {noticeSort.getNoticeNum() - 1, notice.getSortId()};
			//公告数量加1
			query.update(conn, sql, params3);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.out.println("删除公告出错");
			return false;
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	/*public int deleteAllNotice(NoticeSort noticeSort){
		String sql = "delete from t_notice where sortId = ?";
		Object[] params = {noticeSort.getSortId()};
		int c = update(sql, params);
		if (c > 0){
			return c;
		}
		System.out.println("删除公告分类的所有公告出错");
		return -1;
	}*/
	public boolean updateNotice(Notice notice) {
		// TODO Auto-generated method stub
		String sql = "update t_notice set title = ?, content = ? where noticeId = ?";
		Object[] params = {notice.getTitle(),
							notice.getContent(),
							notice.getNoticeId()};
		int c = update(sql, params);
		if (c > 0){
			return true;
		}
		System.out.println("更新公告出错");
		return false;
	}
	public int getNoticeSortNum() {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice_sort";
		return getCountFromTable(sql, null);
	}

	public int getAllPublishedNoticeNum() {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice where status = ?";
		Object[] params = {5};//已发布状态
		int num = getCountFromTable(sql, params);
		return num;
	}
	
	public int getPublishedNoticeNum(NoticeSort noticeSort){
		String sql = "select count(*) count from t_notice where sortId = ? and status = ?";
		Object[] params = {noticeSort.getSortId(), 5};//已发布状态
		int num = getCountFromTable(sql, params);
		return num;
	}

	public int getFinishedNoticeNum() {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice where status = ?";
		Object[] params = {1};
		int num = getCountFromTable(sql, params);
		return num;
	}

	public int getUnPassNoticeNum() {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice where status = ?";
		Object[] params = {4};
		int num = getCountFromTable(sql, params);
		return num;
	}

	public int getRevokedNoticeNum() {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice where status = ?";
		Object[] params = {6};
		int num = getCountFromTable(sql, params);
		return num;
	}

	public Pager getFinishedNotice(Pager page) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where status = ? order by "
				+ "lastUpdateTime desc limit ?, ?";
		Object[] params = {1,
							(page.getCurrentPage() - 1) * page.getPageSize(),
							page.getPageSize()};
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			page.setList(notices);
			return page;
		}
		System.out.println("查询完成编辑公告出错");
		return null;
	}

	public Pager getUnPassNotice(Pager page) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where status = ? order by "
				+ "lastUpdateTime desc limit ?, ?";
		Object[] params = {4,
							(page.getCurrentPage() - 1) * page.getPageSize(),
							page.getPageSize()};
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			page.setList(notices);
			return page;
		}
		System.out.println("查询审核不通过公告出错");
		return null;
	}

	public Pager getRevokedNotice(Pager page) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where status = ? order by "
				+ "lastUpdateTime desc limit ?, ?";
		Object[] params = {6,
							(page.getCurrentPage() - 1) * page.getPageSize(),
							page.getPageSize()};
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			Collections.sort(notices);
			page.setList(notices);
			return page;
		}
		System.out.println("查询已撤销公告出错");
		return null;
	}

	public int getEditableNoticeNum() {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice where status in(?, ?, ?)";
		Object[] params = {1, 4, 6};
		int num = getCountFromTable(sql, params);
		return num;
	}

	public Pager getEditableNotice(Pager page) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where status in(?, ?, ?) "
				+ "order by lastUpdateTime desc limit ?, ?";
		Object[] params = {1, 4, 6,
							(page.getCurrentPage() - 1) * page.getPageSize(),
							page.getPageSize()};
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			Collections.sort(notices);
			page.setList(notices);
			return page;
		}
		System.out.println("查询可编辑公告出错");
		return null;
	}

	public int getEditableNoticeNum(NoticeSort noticeSort) {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice where "
				+ "status in(?, ?, ?) and sortId = ?";
		Object[] params = {1, 4, 6, noticeSort.getSortId()};
		int num = getCountFromTable(sql, params);
		return num;
	}

	public Pager getEditableNotice(NoticeSort noticeSort, Pager page) {
		// TODO Auto-generated method stub
		String sql = "select * from t_notice where status in(?, ?, ?) and "
				+ "sortId = ? order by lastUpdateTime desc limit ?, ?";
		Object[] params = {1, 4, 6, noticeSort.getSortId(),
							(page.getCurrentPage() - 1) * page.getPageSize(),
							page.getPageSize()};
		List<Notice> notices = find(sql, params, Notice.class);
		if (notices != null){
			Collections.sort(notices);
			page.setList(notices);
			return page;
		}
		System.out.println("查询公告分类的可编辑公告出错");
		return null;
	}
	/**
	 * 判断是否是第一次发送审核
	 * @param check
	 * @return
	 */
	public boolean isFirstSendCheck(Check check) {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_check where checkedId = ? and type = ?";
		Object[] params = {check.getCheckedId(), check.getType()};
		int num = getCountFromTable(sql, params);
		if (num == 1){
			return false;
		}else{
			return true;
		}
	}
	/**
	 * 第一次为该公告申请（发送）审核
	 * @param notice 公告对象
	 * @param check 审核对象
	 * @return
	 */
	public boolean firstSendCheck(Notice notice, Check check) {
		// TODO Auto-generated method stub
		String sql = "update t_notice set status = ? where noticeId = ?";
		Object[] params = {2, notice.getNoticeId()};
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			query.update(conn, sql, params);
			sql = "insert into t_check(checkedId, flag, type) values(?, ?, ?)";
			Object[] params2 = {check.getCheckedId(),
								check.getFlag(),
								check.getType()};
			query.update(conn, sql, params2);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.out.println("第一次发送公告审核出错");
			return false;
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 第二次或第三次为该公告申请（发送）审核（其实是修改审核记录）
	 * @param notice
	 * @param check
	 * @return
	 */
	public boolean secondSendCheck(Notice notice, Check check) {
		// TODO Auto-generated method stub
		String sql = "update t_notice set status = ? where noticeId = ?";
		Object[] params = {2, notice.getNoticeId()};
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			query.update(conn, sql, params);
			sql = "update t_check set flag = ? where checkedId = ? and type = ?";
			Object[] params2 = {0,
								check.getCheckedId(),
								check.getType()};
			query.update(conn, sql, params2);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.out.println("更改发送公告审核出错");
			return false;
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public boolean isExistTitle(String title) {
		// TODO Auto-generated method stub
		String sql = "select count(*) count from t_notice where title = ?";
		Object[] params = {title};
		int num = getCountFromTable(sql, params);
		if (num > 0){
			return true;
		}
		return false;
	}
	
}
